﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;
using System.Data.SqlClient;

namespace SWDataDB.DAL
{
    /// <summary>
    /// 2013-09-16:zhhw
    /// </summary>
    public class BBInfo
    {
        // FILE_ID,BB_NAME,PM_PATH_NAME,PM_PATH_NUM,GEO_ATT_NUM,BB_X,BB_Y
        // BB_STYLE,STYLE_CODE,BP_ID,OUT_NAME,INNAR_NAME,IMAGE,REMARK
        public BBInfo() { }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回dataSet</returns>
        public DataSet GetAllInfoBySet()
        {
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        public DataSet SelectByBBID(string bbid)
        {
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL WHERE ID ='";
            strSql += bbid + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }



        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回datatable</returns>
        public DataTable GetAllInfoByTable()
        {
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL";
            return SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到记录for 分页显示，升序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByASC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID ASC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到著录表记录for 分页显示，降序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByDESC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID DESC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="page">页数(第几页)</param>
        /// <param name="num">每页的数目</param>
        /// <returns></returns>
        public IList<BB_INFO_TBL> GetInfoByPage(long page, long num, string strWhere)
        {
            IList<BB_INFO_TBL> bbLst = new List<BB_INFO_TBL>();
            string strSql = SQLHelper.GetPagingSQL("dbo.BB_INFO_TBL", "ID", "*", strWhere, "ID DESC", page, num);
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    BB_INFO_TBL bbInfo = new BB_INFO_TBL();
                    bbInfo.O_ID = int.Parse(dr["ID"].ToString());
                    bbInfo.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    bbInfo.O_BB_CODE = dr["BB_CODE"].ToString();
                    bbInfo.O_BB_NAME = dr["BB_NAME"].ToString();
                    bbInfo.O_PM_PATH_NAME = dr["PM_PATH_NAME"].ToString();
                    bbInfo.O_PM_PATH_NUM = dr["PM_PATH_NUM"].ToString();
                    bbInfo.O_GEO_ATT_NUM = dr["GEO_ATT_NUM"].ToString();
                    if (dr["BB_X"].ToString() != "")                                // 经度
                        bbInfo.O_BB_LAT = Convert.ToInt32(dr["BB_X"].ToString());
                    if (dr["BB_Y"].ToString() != "")                                // 纬度
                        bbInfo.O_BB_LON = Convert.ToInt32(dr["BB_Y"].ToString());
                    bbInfo.O_BB_STYLE = dr["BB_STYLE"].ToString();
                    bbInfo.O_STYLE_CODE = dr["STYLE_CODE"].ToString();
                    bbInfo.O_BP_ID = dr["BP_ID"].ToString();
                    bbInfo.O_OUT_NAME = dr["OUT_NAME"].ToString();
                    bbInfo.O_INNAR_NAME = dr["INNAR_NAME"].ToString();
                    // Image数据暂时不做处理
                    // bbInfo.O_IMAGE = dr["IMAGE"].ToString();
                    bbInfo.O_REMARK = dr["REMARK"].ToString();
                    bbInfo.O_BB_SIZE = int.Parse(dr["BB_SIZE"].ToString());
                    
                    bbLst.Add(bbInfo);
                }

                return bbLst;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 更具FILE_ID查询标本
        /// </summary>
        /// <returns></returns>
        public BB_INFO_TBL SelectbbInfoByID(string bb_code)
        {
            BB_INFO_TBL bbInfo = new BB_INFO_TBL();
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL WHERE BB_CODE ='";
            strSql += bb_code + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    bbInfo.O_BB_CODE = bb_code;
                    bbInfo.O_ID = int.Parse(dr["ID"].ToString());
                    bbInfo.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    bbInfo.O_BB_NAME = dr["BB_NAME"].ToString();
                    bbInfo.O_PM_PATH_NAME = dr["PM_PATH_NAME"].ToString();
                    bbInfo.O_PM_PATH_NUM = dr["PM_PATH_NUM"].ToString();
                    bbInfo.O_GEO_ATT_NUM = dr["GEO_ATT_NUM"].ToString();
                    if (dr["BB_X"].ToString() != "")                                // 经度
                        bbInfo.O_BB_LAT = Convert.ToInt32(dr["BB_X"].ToString());
                    if (dr["BB_Y"].ToString() != "")                                // 纬度
                        bbInfo.O_BB_LON = Convert.ToInt32(dr["BB_Y"].ToString());
                    bbInfo.O_BB_STYLE = dr["BB_STYLE"].ToString();
                    bbInfo.O_STYLE_CODE = dr["STYLE_CODE"].ToString();
                    bbInfo.O_BP_ID = dr["BP_ID"].ToString();
                    bbInfo.O_OUT_NAME = dr["OUT_NAME"].ToString();
                    bbInfo.O_INNAR_NAME = dr["INNAR_NAME"].ToString();
                    // Image数据暂时不做处理
                    // bbInfo.O_IMAGE = dr["IMAGE"].ToString();
                    bbInfo.O_REMARK = dr["REMARK"].ToString();
                    bbInfo.O_BB_SIZE = int.Parse(dr["BB_SIZE"].ToString());
                }

                return bbInfo;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过name查询
        /// </summary>
        /// <param name="bb_name"></param>
        /// <returns></returns>
        public BB_INFO_TBL SelectbbInfoByName(string bb_name)
        {
            BB_INFO_TBL bbInfo = new BB_INFO_TBL();
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL WHERE BB_NAME ='";
            strSql += bb_name + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    bbInfo.O_ID = int.Parse(dr["ID"].ToString());
                    bbInfo.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    bbInfo.O_BB_NAME = bb_name;
                    bbInfo.O_PM_PATH_NAME = dr["PM_PATH_NAME"].ToString();
                    bbInfo.O_PM_PATH_NUM = dr["PM_PATH_NUM"].ToString();
                    bbInfo.O_GEO_ATT_NUM = dr["GEO_ATT_NUM"].ToString();
                    if (dr["BB_X"].ToString() != "")
                        bbInfo.O_BB_LAT = Convert.ToInt32(dr["BB_X"].ToString());
                    if (dr["BB_Y"].ToString() != "")
                        bbInfo.O_BB_LON = Convert.ToInt32(dr["BB_Y"].ToString());
                    bbInfo.O_BB_STYLE = dr["BB_STYLE"].ToString();
                    bbInfo.O_STYLE_CODE = dr["STYLE_CODE"].ToString();
                    bbInfo.O_BP_ID = dr["BP_ID"].ToString();
                    bbInfo.O_OUT_NAME = dr["OUT_NAME"].ToString();
                    bbInfo.O_INNAR_NAME = dr["INNAR_NAME"].ToString();
                    // Image数据暂时不做处理
                    // bbInfo.O_IMAGE = dr["INNAR_NAME"].ToString();
                    bbInfo.O_REMARK = dr["REMARK"].ToString();
/*                    bbInfo.O_BB_SIZE = dr["BB_SIZE"].ToString();*/
                }

                return bbInfo;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 根据外键查询
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <returns></returns>
        public DataSet SelectInfoByFileID(string zl_file_id)
        {
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL WHERE ZL_FILE_ID ='";
            strSql += zl_file_id + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }



        public DataSet SelectInfoByID(int bbid)
        {
            string strSql = "SELECT * FROM dbo.BB_INFO_TBL WHERE ID =";
            strSql += bbid;

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        /// <summary>
        /// 添加新标本
        /// </summary>
        /// <param name="bb_info"></param>
        public int AddbbInfo(BB_INFO_TBL bb_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.BB_INFO_TBL(ZL_FILE_ID,BB_CODE,BB_NAME,PM_PATH_NAME,PM_PATH_NUM,GEO_ATT_NUM,";
            strSql += "BB_X,BB_Y,BB_STYLE,STYLE_CODE,BP_ID,OUT_NAME,INNAR_NAME,BB_SIZE,REMARK) VALUES(";
            strSql += "'" + bb_info.O_ZL_FILE_ID + "',";
            strSql += "'" + bb_info.O_BB_CODE + "',";
            strSql += "'" + bb_info.O_BB_NAME + "',";
            strSql += "'" + bb_info.O_PM_PATH_NAME + "',";
            strSql += "'" + bb_info.O_PM_PATH_NUM + "',";
            strSql += "'" + bb_info.O_GEO_ATT_NUM + "',";
            strSql += "'" + bb_info.O_GEO_ATT_NUM + "',";
            strSql += bb_info.O_BB_LAT.ToString() + ",";
            strSql += bb_info.O_BB_LON.ToString() + ",";
            strSql += "'" + bb_info.O_BB_STYLE + "',";
            strSql += "'" + bb_info.O_STYLE_CODE + "',";
            strSql += "'" + bb_info.O_BP_ID + "',";
            strSql += "'" + bb_info.O_OUT_NAME + "',";
            strSql += "'" + bb_info.O_INNAR_NAME + "',";
//             strSql += "'" + bb_info.O_REMARK + "',";
            strSql += "" + bb_info.O_BB_SIZE + ",";
            strSql += "'" + bb_info.O_REMARK + "')";


            int nRtn = SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);

            // 然后处理二进制数据(暂时没有处理)

            return nRtn;
        }

        /// <summary>
        /// 根据file_id插入IMAGE数据
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="image"></param>
        /// <returns></returns>
        public int AppendImageById(string bb_code, byte[] image)
        {
            return 0;
        }

        /// <summary>
        /// 根据标本名称插入image数据
        /// </summary>
        /// <param name="bb_name"></param>
        /// <param name="image"></param>
        /// <returns></returns>
        public int AppendImageByName(string bb_name, byte[] image)
        {
            return 0;
        }

        /// <summary>
        /// 根据ID删除数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeletebbInfoById(int  id)
        {
            string strSql = "DELETE FROM dbo.BB_INFO_TBL WHERE ID =";
            strSql += id.ToString();

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 根据标本名称删除数据
        /// </summary>
        /// <param name="bb_name"></param>
        /// <returns></returns>
        public int DeletebbInfoByName(string bb_name)
        {
            string strSql = "DELETE FROM dbo.BB_INFO_TBL WHERE BB_NAME ='";
            strSql += bb_name + "'";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }
        
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="bb_info"></param>
        /// <returns></returns>
        public int UpdatebbInfo(BB_INFO_TBL bb_info)
        {
            string strSql = "UPDATE dbo.BB_INFO_TBL ";
            strSql += "SET BB_CODE='" + bb_info.O_BB_CODE + "',";
            strSql += " ZL_FILE_ID='" + bb_info.O_ZL_FILE_ID + "',";
            strSql += " BB_NAME='" + bb_info.O_BB_NAME + "',";
            strSql += " PM_PATH_NAME='" + bb_info.O_PM_PATH_NAME + "',";
            strSql += " PM_PATH_NUM='" + bb_info.O_PM_PATH_NUM + "',";
            strSql += " GEO_ATT_NUM='" + bb_info.O_GEO_ATT_NUM + "',";
            strSql += " CY_POS='" + bb_info.O_CY_POS + "',";
            strSql += " QY_METHOD='" + bb_info.O_QY_METHOD + "',";
            strSql += " CY_LEVEL='" + bb_info.O_CY_LEVEL + "',";
            strSql += " CJ_PEOPLE='" + bb_info.O_CJ_PEOPLE + "',";
            strSql += " CJ_DATE='" + bb_info.O_CJ_DATE.ToString() + "',";
            strSql += " BB_LAT=" + bb_info.O_BB_LAT + ",";
            strSql += " BB_LON=" + bb_info.O_BB_LON + ",";
            strSql += " BB_STYLE='" + bb_info.O_BB_STYLE + "',";

            strSql += " STYLE_CODE='" + bb_info.O_STYLE_CODE + "',";
            strSql += " BP_ID='" + bb_info.O_BP_ID + "',";
            strSql += " OUT_NAME='" + bb_info.O_OUT_NAME + "',";
            strSql += " INNAR_NAME='" + bb_info.O_INNAR_NAME + "',";
            strSql += " REMARK='" + bb_info.O_REMARK + "'";
/*            strSql += "SET BB_SIZE='" + bb_info.O_BB_SIZE + "'";*/
            strSql += "WHERE ID=" + bb_info.O_ID;

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        #region =影像数据操作=
        public int AppendImage(int id, byte[] bImg, long len)
        {
            string SQL = "UPDATE  dbo.BB_INFO_TBL SET IMAGE = @IMAGE WHERE ID=" + id.ToString();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@IMAGE", SqlDbType.Image);
            commandParameters[0].Value = bImg;

            try
            {
                SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, SQL, commandParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }


            return 1;
        }

        public byte[] GetImage(int id)
        {
            byte[] photo = null;
            string sql = "SELECT IMAGE FROM dbo.BB_INFO_TBL WHERE ID =" + id.ToString();
            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.SQLConnString, CommandType.Text, sql, null);//采用SqlDataReader的方法来读取数据

            if (reader.Read())
                photo = reader[0] as byte[];//将第0列的数据写入byte数组 

            reader.Close();                 //关闭reader
            return photo;
        }

        #endregion

    }
}
